<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of BDb
 *
 * @author jianfeng
 */
class BAppDb {

    public function createUser($username, $email, $password, $birthday, $profile) {
        $mUser = new BMUser();
        return $mUser->register($username, $email, $password, $birthday, $profile);
    }

    public function getPersonalService($userId) {

        $services = $this->fetchUserServices($userId);

        foreach ($services as $service) {
            if ($service->getServiceType() == BService::PERSONALSERVICETYPE)
                return $service;
        }

        return FALSE;
    }

    /**
     *
     * @param type $userId
     * @param type $type, include workteam_admin, workteam_member,servedteam_admin,servedteam_member
     */
    protected function fetchUserServices($userId, $roleType = NULL, $withRoles = FALSE) {

        $services = array();
        $serviceRoles = array();

        $sql = "select service_id from bln_service_application_authitem_user u, bln_application_authitem a, 
            bln_authitem_type t where 
            user_id = :UserId and u.authitem_id = a._id and a.authtype = t._id and t.typename = :AuthType";

        //get services with role to user
        $sql1 = "select service_id,authitem_id from bln_service_application_authitem_user where user_id = :UserId";
        //get services with role to user's circle
        $sql2 = "select service_id,authitem_id from 
            bln_service_application_circle_user u,
            bln_service_application_authitem_circle a,
            bln_service_application_circle c 
            where u.user_id = :UserId and  
            a.applicationcircle_id = u.service_application_circle_id and 
            c._id = u.service_application_circle_id";

        //get services with role to user's circles'parent
        $sql3 = "select service_id, authitem_id from 
            bln_service_application_circle c,
            bln_service_application_circle_user u,
            bln_service_application_circle_child p,
            bln_service_application_authitem_circle a 
            where 
            u.user_id = :UserId and 
            c._id = u.service_application_circle_id and 
            c._id = p.child_id and 
            p.parent_id = a.applicationcircle_id";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql1);

        $command->bindParam(":UserId", $userId, PDO::PARAM_INT);

        $dataReader = $command->queryAll();

        foreach ($dataReader as $row) {
            array_push($serviceRoles, array($row['service_id'], $row['authitem_id']));
        }

        $command = $db->createCommand($sql2);

        $command->bindParam(":UserId", $userId, PDO::PARAM_INT);

        $dataReader = $command->queryAll();

        foreach ($dataReader as $row) {
            array_push($serviceRoles, array($row['service_id'], $row['authitem_id']));
        }

        $command = $db->createCommand($sql3);

        $command->bindParam(":UserId", $userId, PDO::PARAM_INT);

        $dataReader = $command->queryAll();

        foreach ($dataReader as $row) {
            array_push($serviceRoles, array($row['service_id'], $row['authitem_id']));
        }

        if ($withRoles == TRUE)
            return $serviceRoles;

        foreach ($serviceRoles as $serviceRole) {
            $serviceId = $serviceRole[0];
            $roleId = $serviceRole[1];
            if ($roleType == NULL)
                $services[$serviceId] = new BService($serviceId);
            else if ($this->isRoleTypeOf($roleId, $serviceId, $roleType))
                $services[$serviceId] = new BService($serviceId);
        }

        return $services;


        return FALSE;
    }

    public function getUserServices($userId, $type) {
        $retServices = array();
        $services = $this->fetchUserServices($userId, $type);

        foreach ($services as $id => $service) {
            if ($service->getServiceType()->typename != BService::PERSONALSERVICETYPE)
                $retServices[$id] = $service;
        }

        return $retServices;
    }

    /**
     * 
     * @param type $userId
     * @return type mix
     * this function return serviceId with roleId list, such as
     * array('serviceId'=>array(...list of role id...))
     */
    public function getUserServicesWithRoles($userId) {
        $servicesWithRoles = array();
        $serviceRoles = $this->fetchUserServices($userId, NULL, TRUE);

        foreach ($serviceRoles as $serviceRole) {
            $serviceId = $serviceRole[0];
            $roleId = $serviceRole[1];
            if (!isset($servicesWithRoles[$serviceId]))
                $servicesWithRoles[$serviceId] = array();
            $rolesId = $servicesWithRoles[$serviceId];
            array_push($rolesId, $roleId);
            $servicesWithRoles[$serviceId] = $rolesId;
        }
        return $servicesWithRoles;
    }

    public function getUserCircles($serviceId, $appId, $userId) {
        $circles = array();

        /*
          $sql = "select c._id from bln_servicecircle c, bln_servicecircle_user u
          where u.user_id = :UserId and c.service_id = :ServiceId";
         */

        $sql = "select ac._id,ac.service_id,ac.app_id,ac.circlename,ac.profile 
            from bln_service_application_circle ac, bln_service_application_circle_user cu 
            where ac._id = cu.service_application_circle_id and ac.service_id = :ServiceId
            and cu.user_id = :UserId and ac.app_id = :AppId";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":UserId", $userId, PDO::PARAM_INT);
        $command->bindParam(":AppId", $appId, PDO::PARAM_INT);
        $command->bindParam(":ServiceId", $serviceId, PDO::PARAM_INT);

        $circleRows = $command->queryAll();

        foreach ($circleRows as $circleRow) {

            if ($circleRow['service_id'] == $serviceId && $circleRow['app_id'] == $appId) {

                $circle = new BCircle($circleRow['_id']);

                $circles[$circleRow['_id']] = $circle;

                $this->getParentCircles($circleRow['_id'], $circles);
            }
        }

        return $circles;
    }

    protected function getParentCircles($circleId, &$circles) {
        //   $circles = array();

        $sql = "select parent_id from bln_service_application_circle_child where child_id = :ChildId";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":ChildId", $circleId, PDO::PARAM_INT);

        $dataReader = $command->queryAll();

        foreach ($dataReader as $row) {

            $circleId = $row[0];

            if ($circleId != null) {
                $circles[$circleId] = new BCircle($circleId);
                $this->getParentCircle($circleId, $circles);
            }
        }
    }

    public function fetchChildAuthItems(&$childRoles, $serviceId, $authItemId) {

        $sql = "select child_id from bln_application_authitem_child where 
            parent_id = :ParentId";

        $sql2 = "select  child_authitem_id from bln_service_authitem_child where 
            parent_authitem_id = :ParentId";


        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":ParentId", $authItemId, PDO::PARAM_INT);

        $dataReader = $command->queryAll();

        foreach ($dataReader as $row) {
            $childRoles[$row['child_id']] = new BAuthItem($row['child_id']);
            $this->fetchChildAuthItems($childRoles, $serviceId, $row['child_id']);
        }

        $command2 = $db->createCommand($sql2);

        $command2->bindParam(":ParentId", $authItemId, PDO::PARAM_INT);

        $dataReader2 = $command2->queryAll();

        foreach ($dataReader2 as $row) {
            $authId = $row['child_authitem_id'];
            $childRoles[$authId] = new BAuthItem($authId);
            $this->fetchChildAuthItems($childRoles, $serviceId, $authId);
        }
    }

    public function fetchParentAuthItems(&$parentRoles, $serviceId, $authItemId) {
        $sql = "select parent_id from bln_application_authitem_child where 
            child_id = :ChildId";

        $sql2 = "select  parent_authitem_id from bln_service_authitem_child where 
            child_authitem_id = :ChildId";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":ChildId", $authItemId, PDO::PARAM_INT);

        $dataReader = $command->queryAll();

        foreach ($dataReader as $row) {
            $parentRoles[$row['parent_id']] = new BAuthItem($row['parent_id']);
            $this->fetchParentAuthItems($parentRoles, $serviceId, $row['parent_id']);
        }

        $command2 = $db->createCommand($sql2);

        $command2->bindParam(":ChildId", $authItemId, PDO::PARAM_INT);

        $dataReader2 = $command2->queryAll();

        foreach ($dataReader2 as $row) {
            $authId = $row['parent_authitem_id'];
            $parentRoles[$authId] = new BAuthItem($authId);
            $this->fetchParentAuthItems($parentRoles, $serviceId, $authId);
        }
    }

    public function addChildRole($parentId, $childId) {
        $sql = "insert into bln_application_authitem_child(parent_id,child_id)
            values(:ParentId,:ChildId)";

        $sql2 = "select count(*) from bln_application_authitem_child where parent_id = :ParentId and child_id = :ChildId";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql2);

        $command->bindParam(":ParentId", $parentId, PDO::PARAM_INT);
        $command->bindParam(":ChildId", $childId, PDO::PARAM_INT);

        $count = $command->queryScalar();

        if ($count <= 0) {
            $command = $db->createCommand($sql);
            $command->bindParam(":ParentId", $parentId, PDO::PARAM_INT);
            $command->bindParam(":ChildId", $childId, PDO::PARAM_INT);
            return $command->execute();
        }

        return $count;
    }

    public function addOperationToRole($operationId, $roleId) {
        $sql = "insert into bln_application_authitem_operation(authitem_id,operation_id)
            values(:RoleId,:OperationId)";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":RoleId", $roleId, PDO::PARAM_INT);
        $command->bindParam(":OperationId", $operationId, PDO::PARAM_INT);

        return $command->execute();
    }

    public function assignUserRole($serviceId, $userId, $roleId) {
        $sql = "insert into bln_service_application_authitem_user(service_id,authitem_id,user_id)
            values(:ServiceId,:RoleId,:UserId)";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":ServiceId", $serviceId, PDO::PARAM_INT);
        $command->bindParam(":RoleId", $roleId, PDO::PARAM_INT);
        $command->bindParam(":UserId", $userId, PDO::PARAM_INT);

        return $command->execute();
    }

    public function assignCircleRole($serviceId, $circleId, $roleId) {
        $sql = "insert into bln_service_application_authitem_circle(service_id,authitem_id,circle_id)
            values(:ServiceId,:RoleId,:CircleId";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":ServiceId", $serviceId, PDO::PARAM_INT);
        $command->bindParam(":RoleId", $roleId, PDO::PARAM_INT);
        $command->bindParam(":CircleId", $circleId, PDO::PARAM_INT);

        return $command->execute();
    }

    public function addServiceChildRole($serviceId, $parentRoleId, $childRoleId) {
        $sql = "insert into bln_service_authitem_child(service_id,parent_authitem_id,child_authitem_id)
            values(:ServiceId,:ParentId,:ChildId)";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":ServiceId", $serviceId, PDO::PARAM_INT);
        $command->bindParam(":ParentId", $parentId, PDO::PARAM_INT);
        $command->bindParam(":ChildId", $childId, PDO::PARAM_INT);

        return $command->execute();
    }

    public function getServiceTypeId($typeName) {
        return BMServiceType::model()->find("typename=:typeName", array(":typeName" => $typeName))->_id;
    }

    public function getServices($condition) {
        $services = array();

        if ($condition == BService::ServiceType_AllTypesExceptPersonal)
            $mServices = BMService::model()->findAllBySql(
                    "select s._id as _id from bln_service s,bln_servicetype t,bln_service_servicetype st where
                        s._id = st.service_id and t._id = st.servicetype_id and t.typename != :TypeName", array(":TypeName" => BService::PERSONALSERVICETYPE));
        else
            $mServices = BMService::model()->findAll();

        foreach ($mServices as $mService) {
            array_push($services, new BService($mService->_id));
        }

        return $services;
    }

    public function getServiceIdByName($serviceName) {
        $service = BMService::model()->findBySql("select * from bln_service where servicename=:ServiceName", array(':ServiceName' => $serviceName));

        return $service->_id;
    }

    public function createService($serviceName, $serviceType = BService::NORMALSERVICETYPE, $serviceCategory = BService::UNKNOWNCATEGORY) {

        $typeId = null;
        $categoryId = null;

        $conn = Yii::app()->db;

        $transaction = $conn->beginTransaction();
        try {

            $insertServiceSql = "insert into bln_service(servicename) values(:ServiceName)";
            $insertServiceTypeSql = "insert into bln_service_servicetype(service_id,servicetype_id) values(:ServiceId,:ServiceTypeId)";
            $insertServiceCategorySql = "insert into bln_service_servicecategory(service_id,servicecategory_id) values(:ServiceId,:ServiceCategoryId)";

            //insert service
            $command1 = $conn->createCommand($insertServiceSql);
            $command1->bindParam(":ServiceName", $serviceName, PDO::PARAM_STR);

            $command1->execute();

            $sql = 'select _id from bln_service where servicename = :ServiceName';

            $newServiceId = $conn->createCommand($sql)->queryScalar(array(':ServiceName' => $serviceName));

            //insert servicetype

            $sql = "select _id from bln_servicetype where typename = :ServiceType";

            if (is_string($serviceType))
                $typeId = $conn->createCommand($sql)->queryScalar(array(':ServiceType' => $serviceType));

            if (is_numeric($serviceType)) {
                $typeId = $serviceType;
            }
            $command2 = $conn->createCommand($insertServiceTypeSql);

            $command2->bindParam(":ServiceId", $newServiceId, PDO::PARAM_INT);
            $command2->bindParam(":ServiceTypeId", $typeId, PDO::PARAM_INT);

            $command2->execute();

            //insert servicecategory

            $sql = "select _id from bln_servicecategory where categoryname = :ServiceCategory";

            if (is_string($serviceCategory))
                $categoryId = $conn->createCommand($sql)->queryScalar(array(':ServiceCategory' => $serviceCategory));

            if (is_numeric($serviceCategory))
                $categoryId = $serviceCategory;

            $command3 = $conn->createCommand($insertServiceCategorySql);

            $command3->bindParam(":ServiceId", $newServiceId, PDO::PARAM_INT);
            $command3->bindParam(":ServiceCategoryId", $categoryId, PDO::PARAM_INT);

            $command3->execute();

            //.... other SQL executions
            $transaction->commit();
        } catch (Exception $e) {
            $transaction->rollBack();
            return false;
        }

        return $newServiceId;
    }

    public function getServiceType($serviceId) {
        $sql = "select typename from bln_servicetype t,bln_service_servicetype s
             where t._id = s.servicetype_id and s.service_id = :ServiceId";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":ServiceId", $serviceId, PDO::PARAM_INT);

        $typename = $command->queryScalar();

        return $typename;
    }

    public function getServiceApps($serviceId) {
        $apps = array();

        $sql = "select app_id from bln_service_application where service_id = :ServiceId";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":ServiceId", $serviceId, PDO::PARAM_INT);

        $dataReader = $command->queryAll();

        foreach ($dataReader as $row) {
            $appClass = $this->getServiceAppClass($row[0]);
            $apps[$row[0]] = new $appClass;
        }

        return $apps;
    }

    public function getServiceAppRoles($appId) {
        $sql = "select * from bln_application_authitem where app_id = :AppId";

        $roles = array();

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":AppId", $appId, PDO::PARAM_INT);

        $dataReader = $command->queryAll();

        foreach ($dataReader as $row) {
            $roles[$row[0]] = new BAuthItem($row[0]);
        }

        return $roles;
    }

    public function getUserRoles($userId, $serviceId, $appId) {

        $sql = "select * from bln_service_application_authitem_user a, bln_application_authitem b
            where a.authitem_id = b._id and b.app_id = :AppId
             and a.service_id = :ServiceId and a.user_id = :UserId";

        $roles = array();

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":ServiceId", $serviceId, PDO::PARAM_INT);
        $command->bindParam(":UserId", $userId, PDO::PARAM_INT);
        $command->bindParam(":AppId", $appId, PDO::PARAM_INT);

        //first get roles in specific app with appId
        $dataReader = $command->queryAll();
        foreach ($dataReader as $row) {
            $roles[$row['authitem_id']] = new BAuthItem($row['authitem_id']);
        }

        //then get user's service role from serviceEngine
        $appId = 1;
        $dataReader = $command->queryAll();
        foreach ($dataReader as $row) {
            $roles[$row['authitem_id']] = new BAuthItem($row['authitem_id']);
        }

        //get roles by with circle
        $circles = $this->getUserCircles($serviceId, $appId, $userId);

        foreach ($circles as $circle) {
            array_merge($roles, $this->getCircleRoles($circle->getId(), $serviceId, $appId));
        }

        return $roles;
    }

    public function getCircleRoles($circleId, $serviceId, $appId) {
        $sql = "select a._id from bln_service_application_authitem_circle a, bln_service_application_circle c 
             where c.app_id = :AppId  and c.service_id = :ServiceId and c._id = a.applicationcircle_id 
             and a.applicationcircle_id = :CircleId";

        $roles = array();

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":AppId", $appId, PDO::PARAM_INT);
        $command->bindParam(":ServiceId", $serviceId, PDO::PARAM_INT);
        $command->bindParam(":CircleId", $circleId, PDO::PARAM_INT);

        $dataReader = $command->queryAll();

        foreach ($dataReader as $row) {
            $roles[$row['authitem_id']] = new BAuthItem($row['authitem_id']);
        }

        return $roles;
    }

    public function getOperations($roleId) {
        $operations = array();

        $sql = "select * from bln_application_authitem_operation where 
             authitem_id = :RoleId";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":RoleId", $roleId, PDO::PARAM_INT);

        $dataReader = $command->queryAll();

        foreach ($dataReader as $row) {
            $operations[$row[2]] = new BOperation($row[2]);
        }

        return $operations;
    }

    public function getRoleTypeId($typeName) {
        $sql = "select _id from bln_authitem_type where 
             typename = :TypeName";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":TypeName", $typeName, PDO::PARAM_STR);

        return $command->queryScalar();
    }

    public function isRoleTypeOf($roleId, $serviceId, $typeName) {
        $sql = "select typename from bln_application_authitem a, 
            bln_authitem_type t
            where 
            a.authtype = t._id and 
            a._id = :RoleId";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":RoleId", $roleId, PDO::PARAM_INT);

        $roleType = $command->queryScalar();

        if ($roleType !== buser::SERVICEMIXEDROLE && $roleType !== $typeName)
            return FALSE;

        if ($roleType == buser::SERVICEMIXEDROLE) {
            $childRoles = array();
            $this->fetchChildAuthItems($childRoles, $serviceId, $roleId);

            foreach ($childRoles as $role) {
                if ($role->getAuthType() == $typeName)
                    return true;
                else
                    $this->isRoleTypeOf($role->getId(), $serviceId, $typeName);
            }

            return FALSE;
        }

        return TRUE;
    }

    public function getRoleType($roleId) {
        
    }

    public function createRole($appId, $name, $type, $showname) {


        $mRole = new BMApplicationAuthItem;

        $typeId = $this->getRoleTypeId($type);

        $mRole->authname = $name;
        $mRole->authtype = $typeId;
        $mRole->app_id = $appId;
        $mRole->showname = $showname;

        $mRole->save();

        return $mRole->_id;
    }

    public function addRole($appId, $name, $type, $showname) {
        $count = -1;

        $sql = "select a._id from bln_application_authitem a,bln_authitem_type b 
            where app_id = :AppId and authname = :RoleName 
            and typename = :AuthType and a.authtype = b._id";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":AppId", $appId, PDO::PARAM_INT);
        $command->bindParam(":RoleName", $name, PDO::PARAM_STR);
        $command->bindParam(":AuthType", $type, PDO::PARAM_STR);

        $count = $command->queryScalar();

        if ($count != NULL && $count > 0) {
            return $count;
        } else {
            $this->createRole($appId, $name, $type, $showname);
        }
    }

    public function getServiceScopeTypeId($scopename) {
        $sql = "select _id from bln_service_scopetype where scopename = :ScopeName";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $command->bindParam(":ScopeName", $scopename, PDO::PARAM_STR);

        return $command->queryScalar();
    }

    public function getVisibleServiceApps($service = null, $serviceType = null, $serviceCategory = null) {

        $apps = array();

        $sql = "select _id from bln_application";

        $db = Yii::app()->getDb();

        $command = $db->createCommand($sql);

        $rows = $command->query();

        foreach ($rows as $row) {

            $app = BServiceApp::getInstanceById($row['_id']);

            if ($app->isVisible($service, $serviceType, $serviceCategory))
                $apps[$row['_id']] = $app;
        }

        return $apps;
    }

    public function addTransitVariable($name, $value, $scope, $type = NULL) {

        if ($type == NULL)
            $type = 'string';

        $db = Yii::app()->getDb();

        $findSql = "select * from bln_transit_variable where name = :Name and scope = :Scope and vtype = :Type";

        $command = $db->createCommand($findSql);

        $command->bindParam(":Name", $name, PDO::PARAM_STR);
        $command->bindParam(":Scope", $scope, PDO::PARAM_STR);
        $command->bindParam(":Type", $type, PDO::PARAM_STR);

        $rows = $command->queryRow();

        //find row, update, otherwise insert
        if (!$rows) {
            $sql = "insert bln_transit_variable(name,value,scope,vtype) 
            values(:Name,:Value,:Scope,:Type)";

            $command = $db->createCommand($sql);

            $command->bindParam(":Name", $name, PDO::PARAM_STR);
            $command->bindParam(":Value", $value, PDO::PARAM_STR);
            $command->bindParam(":Scope", $scope, PDO::PARAM_STR);
            $command->bindParam(":Type", $type, PDO::PARAM_STR);

            return $command->execute();
        } else {
            $sql = "update bln_transit_variable set value = :Value where name = :Name and scope = :Scope and vtype = :Type";
            $command = $db->createCommand($sql);

            $command->bindParam(":Name", $name, PDO::PARAM_STR);
            $command->bindParam(":Value", $value, PDO::PARAM_STR);
            $command->bindParam(":Scope", $scope, PDO::PARAM_STR);
            $command->bindParam(":Type", $type, PDO::PARAM_STR);

            return $command->execute();
        }
    }

    public function getTransitVariable($name, $scope, $type = NULL) {

        if ($type == NULL)
            $type = 'string';

        $sql = "select value from bln_transit_variable where name = :Name and scope = :Scope and vtype = :Type";

        $db = Yii::app()->getDb();
        $command = $db->createCommand($sql);

        $command->bindParam(":Name", $name, PDO::PARAM_STR);
        $command->bindParam(":Scope", $scope, PDO::PARAM_STR);
        $command->bindParam(":Type", $type, PDO::PARAM_STR);

        return $command->queryScalar();
    }

    public function getTransitVariablesByScope($scope, $type = NULL) {

        $sql = "select name,value from bln_transit_variable where scope = :Scope and vtype = :Type";

        if ($type == NULL)
            $sql = "select name,value from bln_transit_variable where scope = :Scope";

        $db = Yii::app()->getDb();
        $command = $db->createCommand($sql);

        $command->bindParam(":Scope", $scope, PDO::PARAM_STR);

        if ($type != NULL)
            $command->bindParam(":Type", $type, PDO::PARAM_STR);

        $rows = $command->query();

        $variables = array();

        foreach ($rows as $row) {
            $variables[$row['name']] = $row['value'];
        }

        return $variables;
    }

    public function deleteTransitVariable($scope, $name = NULL, $type = NULL) {
        $sql = "delete from bln_transit_variable where scope = :Scope";

        if ($name != NULL)
            $sql = "delete from bln_transit_variable where scope = :Scope and name = :Name";

        if ($type != NULL)
            $sql = "delete from bln_transit_variable where scope = :Scope and vtype = :Type";

        if ($name != NULL && $type != NULL)
            $sql = "delete from bln_transit_variable where scope = :Scope and vtype = :Type and name = :Name";


        $db = Yii::app()->getDb();
        $command = $db->createCommand($sql);

        if ($name != NULL)
            $command->bindParam(":Name", $name, PDO::PARAM_STR);

        if ($type != NULL)
            $command->bindParam(":Type", $type, PDO::PARAM_STR);

        if ($type != NULL && $name != NULL) {
            $command->bindParam(":Type", $type, PDO::PARAM_STR);
            $command->bindParam(":Name", $name, PDO::PARAM_STR);
        }

        $command->bindParam(":Scope", $scope, PDO::PARAM_STR);

        return $command->execute();
    }

    public function addOperationRequest($data) {
        $receiver = $data['receiver'];
        $sender = $data['sender'];
        $operation = $data['operation'];
        $operationData = serialize($data['data']);
        $status = $data['status'];

        $sql = "insert bln_operation_request(
            operationname,operationdata,status,start_time,alive_length,
            to_user,to_service,to_service_app,
            from_user,from_service,from_service_app)
            values(
            :OperationName,:OperationData,:Status,:StartTime,:AliveLength,
            :ToUser,:ToService,:ToServiceApp,
            :FromUser,:FromService,:FromServiceApp)";

        $db = Yii::app()->getDb();
        $command = $db->createCommand($sql);

        $command->bindParam(":OperationName", $operation['name'], PDO::PARAM_STR);
        $command->bindParam(":OperationData", $operationData, PDO::PARAM_STR);
        $command->bindParam(":Status", $status['status'], PDO::PARAM_STR);
        $command->bindParam(":StartTime", $status['startTime'], PDO::PARAM_INT);
        $command->bindParam(":AliveLength", $status['aliveLength'], PDO::PARAM_INT);
        $command->bindParam(":ToUser", $receiver['userId'], PDO::PARAM_INT);
        $command->bindParam(":ToService", $receiver['serviceId'], PDO::PARAM_INT);
        $command->bindParam(":ToServiceApp", $receiver['serviceAppId'], PDO::PARAM_INT);
        $command->bindParam(":FromUser", $sender['userId'], PDO::PARAM_INT);
        $command->bindParam(":FromService", $sender['serviceId'], PDO::PARAM_INT);
        $command->bindParam(":FromServiceApp", $sender['serviceAppId'], PDO::PARAM_INT);

        if ($command->execute() > 0)
            return $this->getOperationRequestIdByStartTime($operation['name'], $status['startTime']);
        return FALSE;
    }

    public function getOperationRequestIdByStartTime($operationName, $startTime) {
        $sql = "select * from bln_operation_request where start_time = :StartTime and operationname = :OperationName";

        $db = Yii::app()->getDb();
        $command = $db->createCommand($sql);

        $command->bindParam(":OperationName", $operationName, PDO::PARAM_STR);
        $command->bindParam(":StartTime", $startTime, PDO::PARAM_INT);

        $requestId = $command->queryScalar();
        return $requestId;
    }

    public function addMessageRequest($data) {
        $receiver = $data['receiver'];
        $sender = $data['sender'];
        $operation = $data['operation'];
        $operationData = serialize($data['data']);
        $status = $data['status'];

        $sql = "insert bln_message_request(
            operationname,operationdata,status,start_time,alive_length,
            to_user,to_service,to_service_app,
            from_user,from_service,from_service_app)
            values(
            :OperationName,:OperationData,:Status,:StartTime,:AliveLength,
            :ToUser,:ToService,:ToServiceApp,
            :FromUser,:FromService,:FromServiceApp)";

        $db = Yii::app()->getDb();
        $command = $db->createCommand($sql);

        $command->bindParam(":OperationName", $operation['name'], PDO::PARAM_STR);
        $command->bindParam(":OperationData", $operationData, PDO::PARAM_STR);
        $command->bindParam(":Status", $status['status'], PDO::PARAM_STR);
        $command->bindParam(":StartTime", $status['startTime'], PDO::PARAM_INT);
        $command->bindParam(":AliveLength", $status['aliveLength'], PDO::PARAM_INT);
        $command->bindParam(":ToUser", $receiver['userId'], PDO::PARAM_INT);
        $command->bindParam(":ToService", $receiver['serviceId'], PDO::PARAM_INT);
        $command->bindParam(":ToServiceApp", $receiver['serviceAppId'], PDO::PARAM_INT);
        $command->bindParam(":FromUser", $sender['userId'], PDO::PARAM_INT);
        $command->bindParam(":FromService", $sender['serviceId'], PDO::PARAM_INT);
        $command->bindParam(":FromServiceApp", $sender['serviceAppId'], PDO::PARAM_INT);

        return $command->execute();
    }

    public function getUserIdByLoginId($email) {
        $sql = "select _id from bln_user where email = :Email";

        $db = Yii::app()->getDb();
        $command = $db->createCommand($sql);

        $command->bindParam(":Email", $email, PDO::PARAM_STR);

        return $command->queryScalar();
    }

    public function getUserById($userId) {
        $sql = "select * from bln_user where _id = :UserId";

        $db = Yii::app()->getDb();
        $command = $db->createCommand($sql);

        $command->bindParam(":UserId", $userId, PDO::PARAM_INT);

        return $command->queryRow();
    }

    public function searchService($serviceName, $serviceType, $serviceCategory) {
        if ($serviceCategory != NULL)
            $sql = "select s._id from bln_service s,bln_service_servicetype st, bln_serive_servicecategory sc,bln_servicetype t 
                bln_servicecategory c where 
                s_id = st.service_id and s._id = sc.service_id 
                and st.servicetype_id = t._id and sc.servicecategory_id = c._id 
                and s.servicename = :ServiceName and t.typename = :ServiceType and c.categoryname = :ServiceCategory";
        else {
            $sql = "select s._id from bln_service s,bln_service_servicetype st, bln_servicetype t 
                where 
                s_id = st.service_id  and st.servicetype_id = t._id  
                and s.servicename = :ServiceName and t.typename = :ServiceType";
        }

        $db = Yii::app()->getDb();
        $command = $db->createCommand($sql);

        $command->bindParam(":ServiceName", $serviceName, PDO::PARAM_STR);
        $command->bindParam(":ServiceType", $serviceType, PDO::PARAM_STR);

        if ($serviceCategory != NULL)
            $command->bindParam(":ServiceCategory", $serviceCategory, PDO::PARAM_STR);

        return $command->queryScalar();
    }

    public function getServiceAppId($classname) {

        $app = null;

        $sql = "select * from bln_application where classname = :ClassName";

        $app = BMServiceApplication::model()->findBySql($sql, array(":ClassName" => $classname));

        if ($app == null)
            return FALSE;
        else {
            $id = $app->_id;
            return $id;
        }
    }

    public function getOperationPermits($serviceId, $serviceAppId, $operationId) {
        $sql = "select ao.authitem_id as authitem_id from bln_application_authitem_operation ao, bln_application_authitem aa, bln_service_application sa where
            ao.authitem_id = aa._id and aa.app_id = sa.app_id 
            and sa.service_id = :ServiceId and aa.app_id = :ServiceAppId and ao.operation_id = :OperationId";

        $db = Yii::app()->getDb();
        $command = $db->createCommand($sql);

        $command->bindParam(":ServiceId", $serviceId, PDO::PARAM_INT);
        $command->bindParam(":ServiceAppId", $serviceAppId, PDO::PARAM_INT);
        $command->bindParam(":ServiceAppId", $operationId, PDO::PARAM_INT);

        $dataReader = $command->queryAll();

        $items = array();

        foreach ($dataReader as $row) {
            array_push($items, $row['authitem_id']);
        }

        return $items;
    }

    public function getOperationId($appId, $functionName = NULL, $operationName = NULL) {

        if ($functionName != NULL)
            $sql = "select _id from bln_application_operation where app_id = :AppId and functionname=:FunctionName";

        if ($functionName == NULL && $operationName != NULL)
            $sql = "select _id from bln_application_operation where app_id = :AppId and operationname=:OperationName";

        $db = Yii::app()->getDb();
        $command = $db->createCommand($sql);

        $command->bindParam(":AppId", $appId, PDO::PARAM_INT);

        if ($functionName != NULL)
            $command->bindParam(":FunctionName", $functionName, PDO::PARAM_STR);

        if ($functionName == NULL && $operationName != NULL)
            $command->bindParam(":OperationName", $operationName, PDO::PARAM_STR);

        return $command->queryScalar();
    }

}

?>
